package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.microsoft.sqlserver.jdbc.SQLServerException;

import modellayer.Company;
import modellayer.JobPosition;
import modellayer.KeyPerson;
import modellayer.ZipCode;

/**
 * The KeyPersonDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class KeyPersonDB
{
	private Connection con;
	private boolean fromCompany = false;
	
	/**
	 * KeyPersonDB Fields
	 * @param con
	 * @param fromCompany
	 */
	
	
	/**
	 * Constructor for the KeyPersonDB class, also creates a connection to the SQL Database
	 */
	public KeyPersonDB()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * Search method using keyPersonId
	 * @param keyPersonId
	 * @return singleWhere(wClause)
	 */
	public KeyPerson findKeyPersonById(int keyPersonId)
	{
		String wClause = " keyPersonId='"+keyPersonId+"' ";
		
		return singleWhere(wClause);
	}
	
	
	public boolean deleteKeyPerson(int keyPersonId) throws Exception
	{
		boolean success = false;
		
		String query = "DELETE FROM sem2_KeyPerson WHERE keyPersonId='"+keyPersonId+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);	
			success = true;		
			stmt.close();
		} 
		catch (SQLServerException e) {
			success = true;
		}
		catch (Exception ex) 
		{
			throw new Exception("KeyPerson deletetion went wrong, try again please! Error: "+ex);
		}
		
		return success;
			
	}
	
	/**
	 * Method that accesses the SQL database and prints all keyPersons
	 * @return miscWhere(query)
	 */
	public ArrayList<KeyPerson> listAllKeyPersons()
	{
		String query = "SELECT sem2_KeyPerson.* FROM sem2_KeyPerson";
		
		return miscWhere(query);
	}
	
	/**
	 * Method that accesses the SQL database and finds keypersons from the fromCompany
	 * @param cvr
	 * @param fromCompany
	 * @return miscWhere(query)
	 */
	public ArrayList<KeyPerson> findKeyPersonsByCompany(int cvr, boolean fromCompany)
	{		
		this.fromCompany = fromCompany;
		
		String query = "SELECT kp.* FROM sem2_Company c, sem2_KeyPerson kp, sem2_KeyPersonsCompanies kpc WHERE c.cvr = '"+cvr+"' AND kpc.companyId = c.cvr AND kpc.keyPersonId = kp.keyPersonId";
		
		return miscWhere(query);
	}
	
	/**
	 * miscWhere method that returns an ArrayList with keyPerson objects
	 * @param query
	 * @return kpList
	 */
	private ArrayList<KeyPerson> miscWhere(String query)
	{
		ArrayList<KeyPerson> kpList = new ArrayList<KeyPerson>();
		ResultSet results;
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while (results.next())
			{
				kpList.add(buildKeyPerson(results));
			}
		}
		catch (Exception e)
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+e);
		}
		
		return kpList;
	}
	
	/**
	 * singleWhere method to execute a method in the database
	 * @param wClause
	 * @return keyPersonObj
	 */
	private KeyPerson singleWhere(String wClause)
	{
		ResultSet results;
		KeyPerson keyPersonObj = null;
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next())
			{
				keyPersonObj = buildKeyPerson(results);
			}
			else
			{
				keyPersonObj = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("SQL query: " +query);
			System.out.println("Error: " +e);
		}
		
		return keyPersonObj;
	}
	
	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause)
	{
		String query = "SELECT keyPersonId, name, address, zipcode, phone, mail, interests, jobPositionId, addedDate FROM sem2_KeyPerson";
		
		if (!wClause.isEmpty())
		{
			query += " WHERE " +wClause;
		}
		
		return query;
	}
	
	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return keypPersonObj
	 */
	private KeyPerson buildKeyPerson(ResultSet results)
	{
		KeyPerson keyPersonObj = null;
		
		try
		{
			int zip = results.getInt("zipCode");
			ZipCode zipCodeObj = new ZipcodeDB().findZipcode(zip);
			JobPosition jobPositionObj = new JobPositionDB().findJobPosition(results.getInt("jobPositionId"));
			keyPersonObj = new KeyPerson(results.getInt("keyPersonId"), results.getString("name"), results.getString("address"), zipCodeObj, results.getString("phone"), results.getString("mail"), results.getString("interests"), jobPositionObj, results.getLong("addedDate"));
			
			if (!fromCompany)
			{
				int keyPersonId = results.getInt("keyPersonId");
				ArrayList<Company> cList = new CompanyDB().findCompaniesByKeyPerson(keyPersonId, true);
				
				keyPersonObj.setcList(cList);
			}
		}
		catch(SQLException e)
		{
			System.out.println("Error building KeyPerson object! \n Error: " +e);
		}
		
		return keyPersonObj;
	}
}
